﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;
namespace HCDV.DB
{
    public class DbSql : Idb
    {
        private static string connectionString = BaseConfig.ConnectionString;
        /// <summary>
        /// 获取主键
        /// </summary>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public  long GetSequence(string fieldName)
        {
            SqlParameter parmOut = new SqlParameter();
            parmOut.Direction = System.Data.ParameterDirection.Output;
            parmOut.DbType = System.Data.DbType.Int32;
            // parmOut.ParameterName = "?v_seq_value";
            parmOut.ParameterName = "@v_seq_value";
            SqlParameter parmIn = new SqlParameter();
            parmIn.Direction = System.Data.ParameterDirection.Input;
            parmIn.DbType = System.Data.DbType.String;
            // parmIn.ParameterName = "?v_seq_name";
            parmIn.ParameterName = "@v_seq_name";
            parmIn.Value = fieldName;
            try
            {
                SqlCommand command = new SqlCommand();
                command.Parameters.Add(parmIn);
                command.Parameters.Add(parmOut);
                command.CommandText = "get_sequence";
                command.Connection = new SqlConnection(connectionString);
                command.CommandType = CommandType.StoredProcedure;
                if (command.Connection.State != ConnectionState.Open) command.Connection.Open();
                command.ExecuteNonQuery();
                return long.Parse(parmOut.Value.ToString());
            }
            catch (Exception ex)
            {
                throw ex;

            }
        }
        /// <summary>
        /// 执行指定的prc
        /// </summary>
        /// <param name="procname"></param>
        /// <param name="allinfo"></param>
        /// <param name="info"></param>
        /// <returns></returns>
        public bool doProc(string procname, Hashtable allinfo, out string info)
        {
            info = "";
            try
            {
                SqlCommand command = new SqlCommand();
                command.CommandText = procname;
                command.Connection = new SqlConnection(connectionString); 
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(paramChg("outinfo"), SqlDbType.VarChar, 30);
                //设置参数
                IDictionaryEnumerator en = allinfo.GetEnumerator();  //  遍历哈希表所有的键,读出相应的值
                string value = "";
                string fieldname = "";
                while (en.MoveNext())
                {
                    fieldname = en.Key.ToString();
                    value = en.Value.ToString() == "" ? null : en.Value.ToString();
                    command.Parameters.Add(paramChg(fieldname), SqlDbType.VarChar);
                    command.Parameters[paramChg(fieldname)].Value = value;

                }
                command.Parameters[paramChg("outinfo")].Direction = System.Data.ParameterDirection.Output;
                if (command.Connection.State != ConnectionState.Open) command.Connection.Open();
                command.ExecuteNonQuery();
                info = command.Parameters["@outinfo"].Value.ToString();
                return true;

            }
            catch (Exception ex)
            {
                info = ex.Message;
                throw ex;

            }
        }
        public DataSet getProc(string procname, Hashtable allinfo)
        {

            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlCommand cm = new SqlCommand();
                    cm.Connection = connection;
                    cm.CommandText = procname;
                    cm.CommandType = CommandType.StoredProcedure;
                    IDataParameter[] parameteres = new SqlParameter[allinfo.Count];
                    //设置参数
                    IDictionaryEnumerator en = allinfo.GetEnumerator();  //  遍历哈希表所有的键,读出相应的值
                    int ip = 0;
                    while (en.MoveNext())
                    {
                        parameteres[ip] = new SqlParameter("@" + en.Key.ToString(), (en.Value.ToString() == "" ? null : en.Value.ToString()));
                        ip++;
                    }
                    cm.Parameters.AddRange(parameteres);
                    SqlDataAdapter adapter = new SqlDataAdapter(cm);
                    adapter.Fill(ds);
                    cm.Dispose();
                    return ds;
                }
                catch (Exception e)
                {
                    throw e;
                }
                finally
                {

                    connection.Close();
                }
            }


        }
        /// <summary>
        /// 执行SQL
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int spExe(string sql)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    try
                    {
                        connection.Open();
                        
                        int rows = cmd.ExecuteNonQuery();
                        //if (sql.ToUpper().StartsWith("INSERT"))
                        //{
                        //    int logid = "";
                        //}
                        return rows;
                    }
                    catch (Exception e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        }

        public double spExeBackid(string sql)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                using (SqlCommand cmd = new SqlCommand(sql, connection))
                {
                    try
                    {
                        DataSet ds = new DataSet();
                        connection.Open();
                        int rows = cmd.ExecuteNonQuery();
                        if (rows > 0)
                        {
                            SqlDataAdapter command = new SqlDataAdapter("SELECT LAST_INSERT_ID();", connection);
                            command.Fill(ds, "ds");
                            return (double)ds.Tables[0].Rows[0][0];
                        }
                        else return -1;
                    }
                    catch (Exception e)
                    {
                        connection.Close();
                        throw e;
                    }
                }
            }
        } 
        /// <summary>
        /// 查询SQL
        /// </summary>
        /// <param name="SQLString"></param>
        /// <returns></returns>
        public DataTable query(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds.Tables[0];
            }
        }
        /// <summary>
        /// 查询SQL
        /// </summary>
        /// <param name="SQLString"></param>
        /// <returns></returns>
        public DataSet queryds(string SQLString)
        {
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    SqlDataAdapter command = new SqlDataAdapter(SQLString, connection);
                    command.Fill(ds, "ds");
                }
                catch (Exception ex)
                {
                    throw new Exception(ex.Message);
                }
                return ds;
            }
        }
        /// <summary>
        /// 通用查询-分页
        /// </summary>
        /// <param name="tableinfo"></param>
        /// <param name="returnfiles"></param>
        /// <param name="startp"></param>
        /// <param name="endp"></param>
        /// <param name="where"></param>
        /// <param name="pall"></param>
        /// <param name="orders"></param>
        /// <returns></returns>
        public DataTable getPageInfo(string tableinfo, string returnfiles, string startp, string endp, string where, ref int pall, string orders = null) {
            SqlDataAdapter commandAdp = new SqlDataAdapter();
            SqlCommand command = new SqlCommand();

            command.CommandText = "HC_Simple_Page";
            command.Connection = new SqlConnection(connectionString);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(paramChg("tableViewInfo"), SqlDbType.VarChar);
            command.Parameters.Add(paramChg("returnfiles"), SqlDbType.VarChar);

            command.Parameters.Add(paramChg("startr"), SqlDbType.VarChar);
            command.Parameters.Add(paramChg("endr"), SqlDbType.VarChar);
            command.Parameters.Add(paramChg("wherestring"), SqlDbType.VarChar);
            command.Parameters.Add(paramChg("allrow"), SqlDbType.Int);

            command.Parameters[paramChg("tableViewInfo")].Value = tableinfo;
            command.Parameters[paramChg("returnfiles")].Value = returnfiles;
            command.Parameters[paramChg("startr")].Value = startp;
            command.Parameters[paramChg("endr")].Value = endp;
            command.Parameters[paramChg("wherestring")].Value = where;
            command.Parameters[paramChg("allrow")].Direction = System.Data.ParameterDirection.Output;

            commandAdp.SelectCommand = command;

            DataSet data = new DataSet();
            commandAdp.Fill(data);
            pall = (int)command.Parameters["@allrow"].Value;
            return data.Tables[0];
        }
        public DataTable getPage(string tableinfo, string returnfiles, string startp, string endp, string where, ref double pall, string orders = null)
        {
            SqlDataAdapter commandAdp = new SqlDataAdapter();
            SqlCommand command = new SqlCommand();

            command.CommandText = "HC_Simple_Page";
            command.Connection = new SqlConnection(connectionString);
            command.CommandType = CommandType.StoredProcedure;

            command.Parameters.Add(paramChg("tableViewInfo"), SqlDbType.VarChar);
            command.Parameters.Add(paramChg("returnfiles"), SqlDbType.VarChar);

            command.Parameters.Add(paramChg("startr"), SqlDbType.VarChar);
            command.Parameters.Add(paramChg("endr"), SqlDbType.VarChar);
            command.Parameters.Add(paramChg("wherestring"), SqlDbType.VarChar);
            command.Parameters.Add(paramChg("allrow"), SqlDbType.Int);

            command.Parameters[paramChg("tableViewInfo")].Value = tableinfo;
            command.Parameters[paramChg("returnfiles")].Value = returnfiles;
            command.Parameters[paramChg("startr")].Value = startp;
            command.Parameters[paramChg("endr")].Value = endp;
            command.Parameters[paramChg("wherestring")].Value = where;
            command.Parameters[paramChg("allrow")].Direction = System.Data.ParameterDirection.Output;

            commandAdp.SelectCommand = command;

            DataSet data = new DataSet();
            commandAdp.Fill(data);
            pall = (double)command.Parameters["@allrow"].Value;
            return data.Tables[0];
        }
        /// <summary>
        /// 通用查询-不分页
        /// </summary>
        /// <param name="tableinfo"></param>
        /// <param name="returnfiles"></param>
        /// <param name="orders"></param>
        /// <returns></returns>
        public DataTable getInfo(string tableinfo, string returnfiles,string where, string orders = null)
        {
            int pall=0;
            return getPageInfo( tableinfo,  returnfiles,  "0", "all",  where, ref  pall,  orders) ;
        }
        private string paramChg(string str)
        {
            str = "@" + str;
            return str;
        }


        public DataTable getInfohf(LoginUser l, string ywcode, string tableinfo, string returnfiles, string where, string orders = null)
        {
            double pall;
            return getPagehf(l, ywcode, tableinfo, returnfiles, "0", "all", where, out  pall, orders);
        }
        /// <summary>
        /// 工作流查询
        /// </summary>
        public DataTable getPageInfohf(LoginUser l, string ywcode, string tableinfo, string returnfiles, string startp, string endp, string where, out int pall, string orders = null)
        {
            double palld;
            DataTable dt = getPagehf(l, ywcode, tableinfo, returnfiles, startp, endp, where, out  palld, orders);
            int.TryParse(palld.ToString(), out pall);
            return dt;
        }
        /// <summary>
        /// 工作流查询
        /// </summary>
        public DataTable getPagehf(LoginUser l, string ywcode, string tableinfo, string returnfiles, string startp, string endp, string where, out double pall, string orders = null)
        {
            if(orders ==null)orders=" getdate() desc";
            if (string.IsNullOrEmpty(where)) where = " 1=1 ";
            if (endp == "all")
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT ");
                sb.Append(returnfiles);
                sb.Append(@",hcr_w_run.fdeid as hcrdeid,hcr_w_run.fnid as hcrnid,hcr_w_run.fnname as hcrnname,hcr_w_run.fntype as hcrntype,hcr_w_run.faddtime as hcraddtime,
hcr_w_run.fstates  as hcrstates,hcr_w_run.fywstate as hcrywstate,hcr_w_run.fsetuptime as hcrsetuptime, hcr_w_de.fdename  as hcrdename ");
                sb.Append(" FROM ");
                sb.Append(tableinfo);
                sb.Append(" left join hcr_w_run on hcr_w_run.fruntype='" + ywcode + "'  AND  t.id= hcr_w_run.frunywid   left join  hcr_w_de on  hcr_w_de.id=hcr_w_run.fdeid  ");
                if (!string.IsNullOrEmpty(where))
                {
                    sb.Append(" where ");
                    sb.Append(where);
                }
                sb.Append(";");
               
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        connection.Open();
                        SqlDataAdapter command = new SqlDataAdapter(sb.ToString(), connection);
                        command.Fill(ds, "ds");
                        pall = ds.Tables[0].Rows.Count;
                        return ds.Tables[0];
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }

                }
            }
            else
            {
                StringBuilder sb = new StringBuilder();
                sb.Append("SELECT * FROM ( select ");
                sb.Append(returnfiles);
                sb.Append(@",hcr_w_run.fdeid as hcrdeid,hcr_w_run.fnid as hcrnid,hcr_w_run.fnname as hcrnname,hcr_w_run.fntype as hcrntype,hcr_w_run.faddtime as hcraddtime,
hcr_w_run.fstates  as hcrstates,hcr_w_run.fywstate as hcrywstate,hcr_w_run.fsetuptime as hcrsetuptime, hcr_w_de.fdename  as hcrdename ");
                sb.Append(",ROW_NUMBER() OVER (ORDER BY "+ orders +"  ) AS RANK");
                sb.Append(" FROM ");
                sb.Append(tableinfo);
                sb.Append(" left join hcr_w_run on hcr_w_run.fruntype='" + ywcode + "'  AND  t.id= hcr_w_run.frunywid   left join  hcr_w_de on  hcr_w_de.id=hcr_w_run.fdeid  ");
          
                if (!string.IsNullOrEmpty(where))
                {
                    sb.Append(" where ");
                    sb.Append(where);
                }
                sb.Append(" )  AS TALL where TALL.RANK ");

                sb.Append(" BETWEEN ");
                sb.Append(startp);
                sb.Append(" and  ");
                sb.Append(endp);
                sb.Append(";");
                sb.Append("select count(t.id) from " + tableinfo 
                    + " left join hcr_w_run on hcr_w_run.fruntype='" + ywcode + "'  AND  t.id= hcr_w_run.frunywid   left join  hcr_w_de on  hcr_w_de.id=hcr_w_run.fdeid  " 
                    + "   WHERE  " + where + " order by " + orders + ";");
                using (SqlConnection connection = new SqlConnection(connectionString))
                {
                    DataSet ds = new DataSet();
                    try
                    {
                        connection.Open();
                        SqlDataAdapter command = new SqlDataAdapter(sb.ToString(), connection);
                        command.Fill(ds, "ds");
                        pall = double.Parse(ds.Tables[1].Rows[0][0].ToString());
                        return ds.Tables[0];
                    }
                    catch (Exception ex)
                    {
                        throw new Exception(ex.Message);
                    }

                }
            }
        }
    }
}
